﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using com.vs.DataAccess;
using com.vs.General;

namespace com.vs.DataAccess.DO
{
    public partial class ShippingLoadDO : BaseDO
    {


        #region "Get Model"
        /// <summary>
        /// 将符合数据库主键的持久对象提取到模型对象中，如数据库符合条件的持久对象不存在，则返回空对象
        /// </summary>
        /// <remarks></remarks>
        public static ShippingLoadDO GetModelByPK(Session sess, double iIntLoadNum)
        {
            DataHelper helper = null;
            DataTable dtModel = null;
            string stSQL = SelectSequel + " WHERE INTERNAL_LOAD_NUM = @INTERNAL_LOAD_NUM";

            try
            {
                helper = new DataHelper(sess);
                dtModel = helper.GetTable(CommandType.Text, stSQL, new IDataParameter[]{
                    DataHelper.BuildParameter(sess, "@INTERNAL_LOAD_NUM", iIntLoadNum)
                });
                if (!DataManager.IsEmpty(dtModel))
                {
                    return GetModel(dtModel.Rows[0]);
                }
                else
                {
                    return null;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }

        #endregion

        #region "DataBase Operation"
        /// <summary>
        /// 在数据库里创建该数据访问对象的持久化数据，同时自增长属性INTERNAL_LOAD_NUM也会同时得到更新,并提供数据库事务支持
        /// </summary>
        /// <remarks></remarks>
        public string Insert(Session sess)
        {
            DataHelper helper = null;
            IDataParameter[] paras = null;
            string stSQL = "SHP_IShippingLoad";
            try
            {
                helper = new DataHelper(sess);
                paras = ValueParas(this);
                this.INTERNAL_LOAD_NUM = helper.Insert(CommandType.StoredProcedure, stSQL, paras);
                return "" + this.INTERNAL_LOAD_NUM;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }

        public static void Delete(Session sess, ShippingLoadDO shpLoadDO)
        {
            DataHelper helper = null;
            string stSQL = "SP_SHIPPINGLOAD_DeleteByPK";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.StoredProcedure, stSQL, UpdateWherePara(shpLoadDO));
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }


        }

        /// <summary>
        /// 将该数据访问对象的变化同步到数据库中，如果该数据访问对象在数据库中的持久数据不存在，同步不会发生，同时也不会抛出例外
        /// </summary>
        /// <remarks></remarks>
        public void Update(Session sess)
        {
            DataHelper helper = null;
            string stSQL = "SHP_UShippingLoad";
            try
            {
                helper = new DataHelper(sess);
                helper.Update(CommandType.StoredProcedure, stSQL, ValueParas(this));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }

        public string Update(Session sess, Dictionary<string, object> dcData)
        {
            DataHelper helper = null;
            StringBuilder sbSQL = null;
            string stSQL = null;
            int i = 0;
            IDataParameter[] paras = null;

            try
            {
                if (dcData.Count < 1)
                    return null;
                helper = new DataHelper(sess);
                sbSQL = new StringBuilder(256)
                            .Append(" UPDATE SHIPPING_LOAD SET ");
                paras = new IDataParameter[dcData.Count + 1];
                foreach (KeyValuePair<string, object> kvp in dcData)
                {
                    sbSQL.Append(kvp.Key).Append(" = @Value").Append(i).Append(",");
                    paras[i] = new SqlParameter("@Value" + i, kvp.Value);
                    i++;
                }

                stSQL = sbSQL.ToString();
                stSQL = stSQL.Substring(0, stSQL.Length - 1);

                stSQL = stSQL + " WHERE INTERNAL_LOAD_NUM = @INTERNAL_LOAD_NUM";
                paras[i] = new SqlParameter("@INTERNAL_LOAD_NUM", this.INTERNAL_LOAD_NUM);

                helper.Update(CommandType.Text, stSQL, paras);

                return null;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }
        #endregion

        #region "Data Load"
        /// <summary>
        /// 装载对象SHIPPINGLOAD的所有持久性对象到集合，如果数据量大，调用此方法会造成性能上的问题，请谨慎使用
        /// </summary>
        /// <remarks></remarks>
        public static List<ShippingLoadDO> GetAll(Session sess)
        {
            DataHelper helper = null;
            DataTable dtModel = null;

            try
            {
                helper = new DataHelper(sess);
                dtModel = helper.GetTable(CommandType.Text, SelectSequel);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
                return null;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }


        /// <summary>
        /// 装载指定列的值等于指定值的的所有持久性SHIPPINGLOAD对象到集合
        /// </summary>
        /// <remarks></remarks>
        public static List<ShippingLoadDO> GetListByColumn(Session sess, string columnName, Object value)
        {
            DataHelper helper = null;
            DataTable dtModel = null;
            IDataParameter[] paras = null;
            string stSQL = null;
            try
            {
                helper = new DataHelper(sess);
                stSQL = SelectSequel + " Where [" + columnName + "] =@Value ";
                paras = new IDataParameter[]{
                    new SqlParameter( "@Value", value)
                };
                dtModel = helper.GetTable(CommandType.Text, stSQL, paras);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                ExceptionManager.LogException(sess, e);
                return null;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }

        }


        /// <summary>
        /// 从DataView装载持久性SHIPPINGLOAD对象到集合
        /// </summary>
        /// <remarks></remarks>
        private static List<ShippingLoadDO> LoadListFromDataView(DataView dv)
        {
            List<ShippingLoadDO> list = new List<ShippingLoadDO>();
            for (int index = 0; index <= dv.Count - 1; index++)
            {
                list.Add(GetModel(dv[index].Row));
            }
            return list;
        }

        /// <summary>
        /// loading data value  to property from Datarow
        /// </summary>
        /// <remarks></remarks>
        private static ShippingLoadDO GetModel(DataRow row)
        {
            ShippingLoadDO shpLoadDO = new ShippingLoadDO();
            if (!row["INTERNAL_LOAD_NUM"].Equals(DBNull.Value)) shpLoadDO.INTERNAL_LOAD_NUM = Convert.ToDouble(row["INTERNAL_LOAD_NUM"]);
            if (!row["WAREHOUSE"].Equals(DBNull.Value)) shpLoadDO.WAREHOUSE = Convert.ToString(row["WAREHOUSE"]);
            if (!row["LEADING_STS"].Equals(DBNull.Value)) shpLoadDO.LEADING_STS = Convert.ToDouble(row["LEADING_STS"]);
            if (!row["TRAILING_STS"].Equals(DBNull.Value)) shpLoadDO.TRAILING_STS = Convert.ToDouble(row["TRAILING_STS"]);
            if (!row["CARRIER"].Equals(DBNull.Value)) shpLoadDO.WAREHOUSE = Convert.ToString(row["CARRIER"]);
            if (!row["SCHEDULED_SHIP_DATE"].Equals(DBNull.Value)) shpLoadDO.SCHEDULED_SHIP_DATE = Convert.ToDateTime(row["SCHEDULED_SHIP_DATE"]);
            if (!row["ACTUAL_SHIP_DATE"].Equals(DBNull.Value)) shpLoadDO.ACTUAL_SHIP_DATE = Convert.ToDateTime(row["ACTUAL_SHIP_DATE"]);
            if (!row["LOAD_CLOSED"].Equals(DBNull.Value)) shpLoadDO.LOAD_CLOSED = Convert.ToString(row["LOAD_CLOSED"]);
            if (!row["SEAL_ID"].Equals(DBNull.Value)) shpLoadDO.SEAL_ID = Convert.ToString(row["SEAL_ID"]);
            if (!row["TRAILER_ID"].Equals(DBNull.Value)) shpLoadDO.TRAILER_ID = Convert.ToString(row["TRAILER_ID"]);
            if (!row["TOTAL_SHIPMENTS"].Equals(DBNull.Value)) shpLoadDO.TOTAL_SHIPMENTS = Convert.ToDouble(row["TOTAL_SHIPMENTS"]);
            if (!row["TOTAL_CONTAINERS"].Equals(DBNull.Value)) shpLoadDO.TOTAL_CONTAINERS = Convert.ToDouble(row["TOTAL_CONTAINERS"]);
            if (!row["TOTAL_WEIGHT"].Equals(DBNull.Value)) shpLoadDO.TOTAL_WEIGHT = Convert.ToDouble(row["TOTAL_WEIGHT"]);
            if (!row["WEIGHT_UM"].Equals(DBNull.Value)) shpLoadDO.WEIGHT_UM = Convert.ToString(row["WEIGHT_UM"]);
            if (!row["TOTAL_VOLUME"].Equals(DBNull.Value)) shpLoadDO.TOTAL_VOLUME = Convert.ToDouble(row["TOTAL_VOLUME"]);
            if (!row["VOLUME_UM"].Equals(DBNull.Value)) shpLoadDO.VOLUME_UM = Convert.ToString(row["VOLUME_UM"]);
            if (!row["USER_STAMP"].Equals(DBNull.Value)) shpLoadDO.USER_STAMP = Convert.ToString(row["USER_STAMP"]);
            if (!row["DATE_TIME_STAMP"].Equals(DBNull.Value)) shpLoadDO.DATE_TIME_STAMP = Convert.ToDateTime(row["DATE_TIME_STAMP"]);
            if (!row["ROUTE"].Equals(DBNull.Value)) shpLoadDO.ROUTE = Convert.ToString(row["ROUTE"]);
            if (!row["DOCK_DOOR"].Equals(DBNull.Value)) shpLoadDO.DOCK_DOOR = Convert.ToString(row["DOCK_DOOR"]);
            if (!row["DRIVER"].Equals(DBNull.Value)) shpLoadDO.DRIVER = Convert.ToString(row["DRIVER"]);
            if (!row["MAX_WEIGHT"].Equals(DBNull.Value)) shpLoadDO.MAX_WEIGHT = Convert.ToDouble(row["MAX_WEIGHT"]);
            if (!row["MAX_VOLUME"].Equals(DBNull.Value)) shpLoadDO.MAX_VOLUME = Convert.ToDouble(row["MAX_VOLUME"]);
            //shpLoadDO.IsPersist = true;

            return shpLoadDO;
        }

        #endregion

        #region "Other function"
        /// <summary>
        /// 该数据访问对象从数据库中提取数据的Sql语句 
        /// </summary>
        /// <remarks></remarks>
        private static string SelectSequel
        {
            get
            {
                return "Select * From [SHIPPING_LOAD] ";
            }
        }

        /// <summary>
        /// 该数据访问对象的属性值装载到数据库更新参数数组
        /// </summary>
        /// <remarks></remarks>
        private static IDbDataParameter[] ValueParas(ShippingLoadDO model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@INTERNAL_LOAD_NUM", SqlDbType.Decimal,5),
					new SqlParameter("@WAREHOUSE", SqlDbType.NVarChar,50),
					new SqlParameter("@LEADING_STS", SqlDbType.Decimal,5),
					new SqlParameter("@SCHEDULED_SHIP_DATE", SqlDbType.DateTime),
					new SqlParameter("@ACTUAL_SHIP_DATE", SqlDbType.DateTime),
					new SqlParameter("@LOAD_CLOSED", SqlDbType.NChar,1),
					new SqlParameter("@SEAL_ID", SqlDbType.NVarChar,50),
					new SqlParameter("@TRAILER_ID", SqlDbType.NVarChar,50),
					new SqlParameter("@TOTAL_SHIPMENTS", SqlDbType.Decimal,5),
					new SqlParameter("@TOTAL_CONTAINERS", SqlDbType.Decimal,5),
					new SqlParameter("@TOTAL_WEIGHT", SqlDbType.Decimal,13),
					new SqlParameter("@WEIGHT_UM", SqlDbType.NVarChar,50),
					new SqlParameter("@TOTAL_VOLUME", SqlDbType.Decimal,13),
					new SqlParameter("@VOLUME_UM", SqlDbType.NVarChar,50),
					new SqlParameter("@USER_STAMP", SqlDbType.NVarChar,30),
					new SqlParameter("@DATE_TIME_STAMP", SqlDbType.DateTime),
					new SqlParameter("@ROUTE", SqlDbType.NVarChar,50),
					new SqlParameter("@DOCK_DOOR", SqlDbType.NVarChar,50),
					new SqlParameter("@DRIVER", SqlDbType.NVarChar,50),
                    new SqlParameter("@CARRIER", SqlDbType.NVarChar, 50),
					new SqlParameter("@MAX_WEIGHT", SqlDbType.Decimal,13),
					new SqlParameter("@MAX_VOLUME", SqlDbType.Decimal,13),
					new SqlParameter("@TRAILING_STS", SqlDbType.Decimal,5)};

            parameters[0].Value = model.INTERNAL_LOAD_NUM;
            parameters[1].Value = model.WAREHOUSE;
            parameters[2].Value = model.LEADING_STS;
            parameters[3].Value = model.SCHEDULED_SHIP_DATE;
            parameters[4].Value = model.ACTUAL_SHIP_DATE;
            parameters[5].Value = model.LOAD_CLOSED;
            parameters[6].Value = model.SEAL_ID;
            parameters[7].Value = model.TRAILER_ID;
            parameters[8].Value = model.TOTAL_SHIPMENTS;
            parameters[9].Value = model.TOTAL_CONTAINERS;
            parameters[10].Value = model.TOTAL_WEIGHT;
            parameters[11].Value = model.WEIGHT_UM;
            parameters[12].Value = model.TOTAL_VOLUME;
            parameters[13].Value = model.VOLUME_UM;
            parameters[14].Value = model.USER_STAMP;
            parameters[15].Value = System.DateTime.Now;
            parameters[16].Value = model.ROUTE;
            parameters[17].Value = model.DOCK_DOOR;
            parameters[18].Value = model.DRIVER;
            parameters[19].Value = model.CARRIER;
            parameters[20].Value = model.MAX_WEIGHT;
            parameters[21].Value = model.MAX_VOLUME;
            parameters[22].Value = model.TRAILING_STS;
            return parameters;
        }

        private static IDbDataParameter[] UpdateWherePara(ShippingLoadDO shpLoadDO)
        {
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@INTERNAL_LOAD_NUM", shpLoadDO.INTERNAL_LOAD_NUM);
            return paras;
        }

        #endregion


        public static List<ShippingLoadDO> GetListByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataTable dtModel = null;

            try
            {
                dtModel = GetTableByColumns(sess, dcParam, stOrderBy);
                return LoadListFromDataView(dtModel.DefaultView);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
            }
        }

        public static DataTable GetTableByColumns(Session sess, List<KeyValuePair<string, KeyValuePair<string, object>>> dcParam, string stOrderBy)
        {
            DataHelper helper = null;
            StringBuilder sbSQL = null;
            int i = 0;
            IDataParameter[] pars = null;

            try
            {
                helper = new DataHelper(sess);
                sbSQL = new StringBuilder(SelectSequel)
                    .Append(" WHERE 1=1 ");

                pars = new IDataParameter[dcParam.Count];
                foreach (KeyValuePair<string, KeyValuePair<string, object>> de in dcParam)
                {
                    sbSQL.Append(" AND ").Append(de.Key.ToString()).Append(de.Value.Key).Append("@Value").Append(i);
                    pars[i] = new SqlParameter("@Value" + i, de.Value.Value);
                    i++;
                }

                if (!StringManager.IsEmpty(stOrderBy))
                {
                    sbSQL.Append(stOrderBy);
                }
                return helper.GetTable(CommandType.Text, sbSQL.ToString(), pars);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (helper != null)
                    helper.Dispose();
            }
        }
    }
}
